package com.fsnbigdata.repository;
import com.fsnbigdata.domain.EndangeredSpecies;
import com.fsnbigdata.domain.dto.NamingPair2;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = EndangeredSpeciesRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = EndangeredSpecies.class)
@Transactional(readOnly = true)
public interface EndangeredSpeciesRepository extends DetachableJpaRepository<EndangeredSpecies, Long>, EndangeredSpeciesRepositoryCustom {

    List<NamingPair2> findAllByInitial(String initial);

    Page<EndangeredSpecies> findAll(Specification<EndangeredSpecies> specification, Pageable pageable1);

    @Query(value = "select id,name from endangered_species where initial=?1",nativeQuery = true)
    List<Object> queryByAlphabet(String s);

    @Query(value = "select id,name,latin,distribution,creator,initial,disturbances from endangered_species where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from endangered_species where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1)",nativeQuery = true)
    List<Object> queryAllByKey(String name,String latin, Pageable pageable);

    @Query(value = "select count(*) from endangered_species where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1)",nativeQuery = true)
    Long queryCountByKey(String name,String latin);

    @Query(value = "select * from endangered_species",nativeQuery = true)
    List<Object> getCount();

    @Query(value = "SELECT * FROM endangered_species limit 4",nativeQuery = true)
    List<EndangeredSpecies> queryLimit();

}
